﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace BSPortal
{
    public class ExcelAdo
    {
        public static Boolean GenerateReport(String strFilePath, List<Order> listOrder)
        {
            if (!File.Exists(strFilePath)) return false;
            String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
                                  + "Data Source=" + strFilePath + ";"
                                  + "Extended Properties='Excel 8.0;HDR=Yes'";
            OleDbConnection connExcel = new OleDbConnection(strExcelConn);
            OleDbCommand cmdExcel = new OleDbCommand();
            try
            {
                cmdExcel.Connection = connExcel;

                //Check if the Sheet Exists
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                connExcel.Close();

                #region Check exist table SanPham
                DataRow[] dr = dtExcelSchema.Select("TABLE_NAME = 'SanPham'");
                if (dr != null && dr.Length > 0)
                {
                    #region ClearData
                    cmdExcel.CommandText = "DROP TABLE [SanPham]";
                    connExcel.Open();
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();
                    #endregion
                }
                    #region Create new table
                cmdExcel.CommandText = "CREATE TABLE [SanPham]" + "(MaDatHang int, MaSP int, TenSP varchar(100), Price float, SoLuong int, NCC varchar(50), Tong float);";
                    connExcel.Open();
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();
                    #endregion
                #endregion

                #region Check exist table KhachHang
                DataRow[] dr2 = dtExcelSchema.Select("TABLE_NAME = 'KhachHang'");
                if (dr2 != null && dr2.Length > 0)
                {
                    #region ClearData
                    cmdExcel.CommandText = "DROP TABLE [KhachHang]";
                    connExcel.Open();
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();
                    #endregion
                }
                    #region Create new table
                cmdExcel.CommandText = "CREATE TABLE [KhachHang]" + "(MaDatHang int, KhangHang varchar(50), SoDienThoai varchar(11), ThanhPho varchar(100), Quan varchar(100), Huyen varchar(100), SoNha varchar(100), Phi int, ThanhToan varchar(20), TrangThai varchar(20), GiaTri float, Tong float);";
                    connExcel.Open();
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();
                    #endregion
                #endregion

                foreach (var order in listOrder)
                {
                    var mainService = Common.GetClientService();

                    #region Insert data into table San Pham
                    var listOrderDetail = mainService.GetListOrderDetailByOrderId(order.OrderId);
                    double total = 0;
                    foreach (var orderDetail in listOrderDetail)
                    {
                        var product = mainService.GetFullProductById(orderDetail.ProductId);
                        string data = string.Format("INSERT INTO [SanPham] (MaDatHang, MaSP, TenSP, Price, SoLuong, NCC, Tong)" + " values ({0}, {1}, '{2}', {3}, {4}, '{5}', {6})",
                            order.OrderId, product.ProductId, product.Name, Math.Round(product.Price * (1 - (double)product.Discount)), orderDetail.Amount, product.Distributor, Math.Round(product.Price * (1 - (double)product.Discount)) * orderDetail.Amount);

                        cmdExcel.CommandText = data;
                        connExcel.Open();
                        cmdExcel.ExecuteNonQuery();
                        total += Math.Round(product.Price*(1 - (double) product.Discount));
                        connExcel.Close();
                    }

                    string city = string.Empty, district = string.Empty, ward = string.Empty;
                    try
                    {
                        city = mainService.GetCityById((int)order.City).Name;
                        district = mainService.GetDistrictById((int)order.District).Name;
                        ward = mainService.GetWardById((int)order.Ward).Name;
                    }
                    catch (Exception ex)
                    {
                    }

                    string orderInsert = string.Format("INSERT INTO [KhachHang] (MaDatHang, KhangHang, SoDienThoai, ThanhPho, Quan, Huyen, SoNha, Phi, ThanhToan, TrangThai, GiaTri, Tong)" + " values ({0}, '{1}', '{2}', '{3}', '{4}', '{5}','{6}',{7},'{8}','{9}',{10},{11})",
                        order.OrderId, order.Name, order.Phone, city, district, ward, order.Address, order.Fee, Common.GetOrderPayString((int)order.PayType), Common.GetOrderStatusString((int)order.Status), total, total + order.Fee);

                    cmdExcel.CommandText = orderInsert;
                    connExcel.Open();
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();

                    #endregion
                }

                return true;
            }
            catch (Exception exception)
            {
                return false;
            }
            finally
            {
                cmdExcel.Dispose();
                connExcel.Dispose();
            }
        }
    }
}